﻿using Dapper;
using OddUserManager.Data.IRepositories;
using OddUserManager.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OddUserManager.Data.Repositories
{
    public class RealTimeMatchsRepository : RealTimeMacthDapperBaseRepository<RealTimeMatchs, int>, IRealTimeMatchsRepository
    {

        private readonly string matchs = "MicroDataBaseDB.dbo.Matchs";

        public RealTimeMatchs Get(int id)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<RealTimeMatchs> GetAllList()
        {
            throw new NotImplementedException();
        }

        public List<RealTimeMatchs> GetAllMatchsByParam(RealTimeMatchsParam param)
        {
            List<RealTimeMatchs> list = null;
            if (param == null) return list;
            if (param.RecentMatchCount < 1 || param.RecentMatchCount > 100)
            {
                param.RecentMatchCount = 30;
            }

            using (var con = GetOpenConnection())
            {

                var sql = string.Format(@" select  id as Id, bet007_id as MatchId, league_id as LeagueId, season_id as SeasonId, league_name as LeagueName,
                                    match_season as MatchSeason, curr_round as CurrRound, color_value as ColorValue, host_team_id as HostTeamId, host_team_name as HostTeamName, host_rank as HostRank, 
                                    guest_team_id as GuestTeamId, guest_team_name as GuestTeamName, guest_rank as GuestRank, match_time as MatchTime, match_year as MatchYear, status as Status, 
                                    host_score as HostScore, host_half_score as HostHalfScore, host_red_card as HostRedCard, host_yellow_card as HostYellowCard, guest_score as GuestScore, guest_half_score as GuestHalfScore,
                                    guest_red_card as GuestRedCard, guest_yellow_card as GuestYellowCard, tv_channel as TvChannel, win_odds as WinOdds, drow_odds as DrowOdds, lose_odds as LoseOdds, 
                                    asia_tape as AsiaTape, score_tape as ScoreTape, group_index as GroupIndex, team_mark as TeamMark, removed as Removed, create_time as CreateTime, last_update_time as LastUpdateTime, 
                                    league_type as LeagueType, neutral as Neutral, host_away_reverse as HostAwayReverse, half_start_time as HalfStartTime, host_120_score as Host120Score, host_spot_score as HostSpotScore,
                                    guest_120_score as Guest120Score, guest_spot_score as GuestSpotScore, match_type as MatchType, updateTime as UpdateTime from {0} t 
                                    where 1=1 ", matchs);

                if (param.HostOrGuest == (int)HostOrGuestType.全部)
                {
                    sql += $" and (t.host_team_id = {param.TeamId} or t.guest_team_id = {param.TeamId}) ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.主场)
                {
                    sql += $"  and  t.host_team_id = {param.TeamId} ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.客场)
                {
                    sql += $" and t.guest_team_id = {param.TeamId} ";
                }
                else
                {
                    sql += " and 1=0 ";
                }

                if (param.LeagueIds != null && param.LeagueIds.Count > 0)
                {
                    sql += $" and t.league_id in ({string.Concat(param.LeagueIds, ",")})";
                }

                if (param.MatchId > 0) {
                    sql += $" and t.bet007_id = {param.MatchId} ";
                }

                list = con.Query<RealTimeMatchs>(sql, null, null, true, 3000, null).ToList();
            }

            return list;
        }

        public List<RealTimeLeagueInfo> GetLeagueInfosByParam(RealTimeMatchsParam param)
        {
            List<RealTimeLeagueInfo> list = null;
            if (param == null) return list;
            if (param.RecentMatchCount < 1 || param.RecentMatchCount > 100)
            {
                param.RecentMatchCount = 30;
            }

            using (var con = GetOpenConnection())
            {

                var whereSql = $" match_time <= '{DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")}' ";
                if (param.HostOrGuest == (int)HostOrGuestType.全部)
                {
                    whereSql += $" and (host_team_id = {param.TeamId} or guest_team_id = {param.TeamId}) ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.主场)
                {
                    whereSql += $"  and  host_team_id = {param.TeamId} ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.客场)
                {
                    whereSql += $" and guest_team_id = {param.TeamId} ";
                }
                else
                {
                    whereSql += " and 1=0 ";
                }

                var sql = string.Format(@"select  a1.LeagueId,a2.LeagueName,a2.Id from 
                        ( select league_id as LeagueId,count(1) as LeagueCount,max(match_time) as MatchTime from
                            (select top {0} * from {1} where {2} order by match_time desc ) t group by league_id ) a1
                                left join LeagueInfo a2 on a1.LeagueId = a2.LeagueId order by a1.MatchTime desc",param.RecentMatchCount,matchs,whereSql);

                list = con.Query<RealTimeLeagueInfo>(sql, null, null, true, 3000, null).ToList();
            }

            return list;
        }

        public List<RealTimeMatchs> GetMatchsByParam(RealTimeMatchsParam param)
        {
            List<RealTimeMatchs> list = null;
            if (param == null) return list;
            if (param.RecentMatchCount < 1 || param.RecentMatchCount > 100) {
                param.RecentMatchCount = 30;
            }

            using (var con = GetOpenConnection())
            {

                var sql = string.Format(@" select a1.*,(9200/a2.CutWinChance) as CutWinChance,(9200/a2.CutDrawChance) as CutDrawChance,(9200/a2.CutLoseChance) as CutLoseChance from ( select top {0} id as Id, bet007_id as MatchId, league_id as LeagueId, season_id as SeasonId, league_name as LeagueName,
                                    match_season as MatchSeason, curr_round as CurrRound, color_value as ColorValue, host_team_id as HostTeamId, host_team_name as HostTeamName, host_rank as HostRank, 
                                    guest_team_id as GuestTeamId, guest_team_name as GuestTeamName, guest_rank as GuestRank, match_time as MatchTime, match_year as MatchYear, status as Status, 
                                    host_score as HostScore, host_half_score as HostHalfScore, host_red_card as HostRedCard, host_yellow_card as HostYellowCard, guest_score as GuestScore, guest_half_score as GuestHalfScore,
                                    guest_red_card as GuestRedCard, guest_yellow_card as GuestYellowCard, tv_channel as TvChannel, win_odds as WinOdds, drow_odds as DrowOdds, lose_odds as LoseOdds, 
                                    asia_tape as AsiaTape, score_tape as ScoreTape, group_index as GroupIndex, team_mark as TeamMark, removed as Removed, create_time as CreateTime, last_update_time as LastUpdateTime, 
                                    league_type as LeagueType, neutral as Neutral, host_away_reverse as HostAwayReverse, half_start_time as HalfStartTime, host_120_score as Host120Score, host_spot_score as HostSpotScore,
                                    guest_120_score as Guest120Score, guest_spot_score as GuestSpotScore, match_type as MatchType, updateTime as UpdateTime from {1} t 
                                    where 1=1 ", param.RecentMatchCount,matchs);

                if (param.HostOrGuest == (int)HostOrGuestType.全部)
                {
                    sql += $" and (t.host_team_id = {param.TeamId} or t.guest_team_id = {param.TeamId}) ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.主场)
                {
                    sql += $"  and  t.host_team_id = {param.TeamId} ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.客场)
                {
                    sql += $" and t.guest_team_id = {param.TeamId} ";
                }
                else {
                    sql += " and 1=0 ";
                }

                if (param.LeagueIds != null && param.LeagueIds.Count > 0)
                {
                    sql += $" and t.league_id in ({string.Concat(param.LeagueIds,",")})"; 
                }

                sql += $" and t.match_time <= '{DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")}' order by match_time desc ) a1 left join CutOdds a2 on  a1.MatchId = a2.MatchId and a2.SectionNumber = 1 order by a1.MatchTime desc";

                list = con.Query<RealTimeMatchs>(sql,null,null,true,3000,null).ToList();
            }

            return list;

        }

        public List<RealTimeMatchs> GetMatchTeamInfos(RealTimeMatchsParam param)
        {
            List<RealTimeMatchs> list = null;
            if (param == null) return list;
            using (var con = GetOpenConnection())
            {
                var sql = string.Format(@" select a1.*,(9200/a2.CutWinChance) as CutWinChance,(9200/a2.CutDrawChance) as CutDrawChance,(9200/a2.CutLoseChance) as CutLoseChance,
                                    a3.TeamRank,a3.TeamIntegral,a3.AllCount,a3.WinCount,a3.DrawCount,a3.LoseCount,a3.InCount,a3.OutCount,a3.GoalCount,
                                    a3.HostIntegral,a3.HostAllCount,a3.HostWinCount,a3.HostDrawCount,a3.HostLoseCount,a3.HostInCount,a3.HostOutCount,a3.HostGoalCount,a3.HostRank as HostTeamRank,
                                    a3.GuestIntegral,a3.GuestAllCount,a3.GuestWinCount,a3.GuestDrawCount,a3.GuestLoseCount,a3.GuestInCount,a3.GuestOutCount,a3.GuestGoalCount,a3.GuestRank as GuestTeamRank
                                    from ( select id as Id, bet007_id as MatchId, league_id as LeagueId, season_id as SeasonId, league_name as LeagueName,
                                    match_season as MatchSeason, curr_round as CurrRound, color_value as ColorValue, host_team_id as HostTeamId, host_team_name as HostTeamName, host_rank as HostRank, 
                                    guest_team_id as GuestTeamId, guest_team_name as GuestTeamName, guest_rank as GuestRank, match_time as MatchTime, match_year as MatchYear, status as Status, 
                                    host_score as HostScore, host_half_score as HostHalfScore, host_red_card as HostRedCard, host_yellow_card as HostYellowCard, guest_score as GuestScore, guest_half_score as GuestHalfScore,
                                    guest_red_card as GuestRedCard, guest_yellow_card as GuestYellowCard, tv_channel as TvChannel, win_odds as WinOdds, drow_odds as DrowOdds, lose_odds as LoseOdds, 
                                    asia_tape as AsiaTape, score_tape as ScoreTape, group_index as GroupIndex, team_mark as TeamMark, removed as Removed, create_time as CreateTime, last_update_time as LastUpdateTime, 
                                    league_type as LeagueType, neutral as Neutral, host_away_reverse as HostAwayReverse, half_start_time as HalfStartTime, host_120_score as Host120Score, host_spot_score as HostSpotScore,
                                    guest_120_score as Guest120Score, guest_spot_score as GuestSpotScore, match_type as MatchType, updateTime as UpdateTime from {0} t 
                                    where 1=1 ",  matchs);

                if (param.HostOrGuest == (int)HostOrGuestType.主场)
                {
                    sql += $"  and  t.host_team_id = {param.TeamId} ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.客场)
                {
                    sql += $" and t.guest_team_id = {param.TeamId} ";
                }
                else
                {
                    sql += " and 1=0 ";
                }

                if (param.LeagueId > 0)
                {
                    sql += $" and t.league_id = {param.LeagueId}";
                }
                else
                {
                    sql += " and 1=0 ";
                }

                if (param.SeasonId > 0)
                {
                    sql += $" and t.season_id = {param.SeasonId}";
                }
                else {
                    sql += " and 1=0 ";
                }

                sql += $" ) a1 left join CutOdds a2 on  a1.MatchId = a2.MatchId and a2.SectionNumber = 1 ";

                if (param.HostOrGuest == (int)HostOrGuestType.主场) {
                    sql += $" left join TeamRankInfo a3 on a1.LeagueId = a3.LeagueId and a3.SeasonId ={param.LastSeasonId} and a1.GuestTeamId = a3.TeamId  ";
                } else if (param.HostOrGuest == (int)HostOrGuestType.客场) {
                    sql += $" left join TeamRankInfo a3 on a1.LeagueId = a3.LeagueId and a3.SeasonId = {param.LastSeasonId} and a1.HostTeamId = a3.TeamId  ";
                }

                list = con.Query<RealTimeMatchs>(sql, null, null, true, 3000, null).ToList();
            }

            if (list != null && list.Count > 0) {
                list = list.OrderBy(p=>p.TeamRank).ToList();
            }
            return list;
        }

        public List<RealTimeSeasonInfo> GetSeasonsByParam(RealTimeMatchsParam param)
        {
            List<RealTimeSeasonInfo> list = null;
            if (param == null) return list;
            using (var con = GetOpenConnection())
            {
                var whereSql = " 1=1 ";
                
                if (param.LeagueId > 0)
                {
                    whereSql += $" and LeagueId = {param.LeagueId} ";
                }
                else
                {
                    whereSql += " and 1=0 ";
                }

                var sql = string.Format(@"select * from SeasonInfo where {0} order by StartTime desc", whereSql);

                list = con.Query<RealTimeSeasonInfo>(sql, null, null, true, 3000, null).ToList();

            }
            return list;
        }

        public List<RealTimeLeagueInfo> GetTeamLeagues(RealTimeMatchsParam param)
        {
            List<RealTimeLeagueInfo> list = null;
            if (param == null) return list;
            using (var con = GetOpenConnection())
            {
                var whereSql = $" match_time <= '{DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")}' and season_id is not null and league_id > 0  ";
                if (param.HostOrGuest == (int)HostOrGuestType.全部)
                {
                    whereSql += $" and (host_team_id = {param.TeamId} or guest_team_id = {param.TeamId}) ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.主场)
                {
                    whereSql += $"  and  host_team_id = {param.TeamId} ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.客场)
                {
                    whereSql += $" and guest_team_id = {param.TeamId} ";
                }
                else
                {
                    whereSql += " and 1=0 ";
                }

                var sql = string.Format(@"select  a1.LeagueId,a2.LeagueName,a2.Id from 
                        ( select league_id as LeagueId,count(1) as LeagueCount,max(match_time) as MatchTime from
                            (select * from {0} where {1} ) t group by league_id ) a1
                                inner join LeagueInfo a2 on a1.LeagueId = a2.LeagueId order by a1.MatchTime desc", matchs, whereSql);

                list = con.Query<RealTimeLeagueInfo>(sql, null, null, true, 3000, null).ToList();
            }
            return list;
        }

        public RealTimeTeamRankInfo GetTeamRankInfoByParam(RealTimeMatchsParam param)
        {
            RealTimeTeamRankInfo model = null;

            using (var con = GetOpenConnection())
            {
                var sql = "select * from TeamRankInfo where 1=1 ";
                if (param.LeagueId > 0) {
                    sql += $" and LeagueId = {param.LeagueId} ";
                }
                if (param.SeasonId > 0) {
                    sql += $" and SeasonId = {param.SeasonId} ";
                }
                if (param.TeamId > 0) {
                    sql += $" and TeamId = {param.TeamId} ";
                }

                model = con.Query<RealTimeTeamRankInfo>(sql, null, null, true, 3000, null).FirstOrDefault();

            }

            return model;
        }

        public List<RealTimeSeasonInfo> GetTeamSeasons(RealTimeMatchsParam param)
        {
            List<RealTimeSeasonInfo> list = null;
            if (param == null) return list;
            using (var con = GetOpenConnection())
            {
                var whereSql = $" match_time <= '{DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")}' ";
                if (param.HostOrGuest == (int)HostOrGuestType.全部)
                {
                    whereSql += $" and (host_team_id = {param.TeamId} or guest_team_id = {param.TeamId}) ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.主场)
                {
                    whereSql += $"  and  host_team_id = {param.TeamId} ";
                }
                else if (param.HostOrGuest == (int)HostOrGuestType.客场)
                {
                    whereSql += $" and guest_team_id = {param.TeamId} ";
                }
                else
                {
                    whereSql += " and 1=1 ";
                }
                if (param.LeagueId > 0)
                {
                    whereSql += $" and league_id = {param.LeagueId} ";
                }
                else {
                    whereSql += " and 1=0 ";
                }

                var sql = string.Format(@"select  a2.* from 
                    ( select season_id as SeasonId,count(1) as SeasonCount,max(match_time) as MatchTime from
                        (select * from {0}  where  {1} ) t  group by season_id ) a1
                      inner join SeasonInfo a2 on a1.SeasonId = a2.SeasonId order by a2.StartTime desc", matchs, whereSql);

                list = con.Query<RealTimeSeasonInfo>(sql, null, null, true, 3000, null).ToList();

            }
            return list;
        }




        public bool InsertOrUpdate(RealTimeMatchs entity)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<RealTimeMatchs> LoadPageList(int startPage, int pageSize, out int rowCount, Func<RealTimeMatchs, bool> where, Func<RealTimeMatchs, object> order)
        {
            throw new NotImplementedException();
        }


    }
}
